package ha.web.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class DBConnector {
	
	private static DBConnector singleton = null;
	private final static String dbUrl = "jdbc:mysql://localhost:3306/game_db";
    private final static String dbUser = "root";
    private final static String dbPassword = "1033";
    private static Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;
    
	private DBConnector () {
		InitDB();
		InitTable();	
    }

	private void InitTable() {
		try {
			con = getConnection();
			st = con.createStatement();
			DatabaseMetaData metadata = con.getMetaData();
			rs = metadata.getTables(null, null, "userlogin", null);
			if(rs.next())
			    return;
			String createTableString = "CREATE TABLE userlogin "+
            		"(user_name VARCHAR(25) NOT NULL," +
            		"password VARCHAR(25) NOT NULL,"+
            		"user_id INT(50) NOT NULL," +
            		"PRIMARY KEY ( user_name ))";
            st.executeUpdate(createTableString);
        } catch (SQLException ex) {
           System.out.println(ex.getMessage());
           System.exit(1);

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
            	System.out.println(ex.getMessage());
                System.exit(1);
            }
        }
	}

	private void InitDB() {
		try{
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", dbUser,dbPassword);
			rs = con.getMetaData().getCatalogs();
			while (rs.next()) {
				String databaseName = rs.getString(1);
	            if(databaseName.equals("game_db")){
	            	if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	                if (con != null) {
	                    con.close();
	                }
	            	return;
	            }
	        }
			Statement st = con.createStatement();
			st.executeUpdate("CREATE DATABASE game_db");
	    }
	    catch(Exception e){
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null)
	                rs.close();
	            if (st != null) 
	                st.close();
	            if (con != null) 
	                con.close();
	            
	        } catch (SQLException ex) {
	        	System.out.println(ex.getMessage());
	            System.exit(1);
	        }
	    }

	}
	public static DBConnector getInstance() {
		if(singleton==null)
			singleton = new DBConnector();
		return singleton;
	}
	
	 
	private Connection getConnection() {
        try {
			con = DriverManager.getConnection(dbUrl, dbUser,dbPassword);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
        return con;
    }

	public static boolean IsUserAtDB(UserId user) {
		PreparedStatement ptmt = null;
	    ResultSet resultSet = null;
		String queryString = 
				"SELECT password FROM userlogin WHERE user_name = ?";
		try {
			con = getInstance().getConnection();
			ptmt = con.prepareStatement(queryString);
			ptmt.setString(1, user.getName());
			resultSet = ptmt.executeQuery();
			if (resultSet.next() && 
					resultSet.getString("password").equals(user.getPass())) {	
				con.close();
				return true;
			}
			con.close();	
		} catch (SQLException e) {
			return false;
//			e.printStackTrace();
//			System.exit(1);
		}
		
		return false;
	}

	public static boolean addUserAtDB(UserId user) {
		PreparedStatement ptmt = null;
	    ResultSet resultSet = null;
		String queryString = 
				"SELECT user_name FROM userlogin WHERE user_name = ?";
		try {
			Connection conn = getInstance().getConnection();
			ptmt = conn.prepareStatement(queryString);
			ptmt.setString(1, user.getName());
			resultSet = ptmt.executeQuery();
			if (resultSet.next() && 
					resultSet.getString("user_name").equals(user.getName())) {	
				conn.close();
				return false;
			}
			queryString = "INSERT INTO userlogin (user_name, password,user_id) VALUES (?,?,?)";
			ptmt = conn.prepareStatement(queryString);
			ptmt.setString(1, user.getName());
			ptmt.setString(2, user.getPass());
			ptmt.setInt(3, user.getUserID());
			ptmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			return false;
//			e.printStackTrace();
//			System.exit(1);
		}
	}

	
	

	
	
}
